package organizer.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import organizer.ICancelable;

/**
 *
 * @author dawong
 */
public class JdbcUtil {

    private static final Logger log = Logger.getLogger(JdbcUtil.class);
    
    private String url;
    private Properties props = new Properties();
    private DataSource dataSource;

    public static JdbcUtil instance(DataSource dataSource) {
        return new JdbcUtil(dataSource);
    }

    private JdbcUtil() {
    }

    private JdbcUtil(String url, String user, String password) {
        this.url = url;
        props.setProperty("user", user);
        props.setProperty("password", password);
    }

    private JdbcUtil(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    private Connection getConnection() throws SQLException {
        if (dataSource != null) {
            return dataSource.getConnection();
        } else if (url != null) {
            return DriverManager.getConnection(url, props);
        }
        throw new RuntimeException("Can't create connection");
    }

    private void connect(RequiresConnection request) {
        Connection c = null;
        try {
            c = getConnection();
            request.connect(c);
        } catch (SQLException e) {
            log.error(null, e);
        } finally {
            if (c != null) {
                try { c.close(); } catch (SQLException e) { log.error(null, e); }
            }
        }
    }

    public void exec(final String sql, final JdbcPreparedCallback callback) throws SQLException {
        connect(new RequiresConnection() {

            public void connect(Connection c) throws SQLException {
                PreparedStatement ps = c.prepareStatement(sql);
                if (callback != null)
                    callback.setParams(ps);
                ps.execute();
                ps.close();
            }
        });
    }

    public <T> T unique(final String sql, final JdbcPreparedResultsCallback<T> callback) throws SQLException {
        ValuedConnector<T> c = new ValuedConnector<T>() {

            @Override
            public T query(Connection c) throws SQLException {
                PreparedStatement ps = c.prepareStatement(sql);
                callback.setParams(ps);
                ResultSet rs = ps.executeQuery();
                T result = null;
                if (rs.next()) {
                    result = callback.getRow(rs);
                }
                rs.close();
                ps.close();
                return result;
            }
        };
        connect(c);
        return c.getValue();
    }

    public int insert(final String sql, final JdbcPreparedCallback callback) throws SQLException {
        ValuedConnector<Integer> c = new ValuedConnector<Integer>() {

            @Override
            public Integer query(Connection c) throws SQLException {
                PreparedStatement ps = c.prepareStatement(sql);
                callback.setParams(ps);
                ps.executeUpdate();
                Integer result = ps.RETURN_GENERATED_KEYS;
                ps.close();
                return result;
            }
        };
        connect(c);
        return c.getValue();
    }

    public void each(final String sql, final JdbcPreparedEachCallback callback, final ICancelable cancel) throws SQLException {
        connect(new RequiresConnection() {

            public void connect(Connection c) throws SQLException {
                PreparedStatement ps = c.prepareStatement(sql);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    if (cancel != null && cancel.isCanceled())
                        break;
                    callback.row(rs);
                }
                rs.close();
                ps.execute();
                ps.close();
            }
        });
    }

    public <T> List<T> list(final String sql, final JdbcPreparedResultsCallback<T> callback) throws SQLException {
        return list(sql, callback, null);
    }

    public <T> List<T> list(final String sql, final JdbcPreparedResultsCallback<T> callback, final ICancelable cancel) throws SQLException {
        ValuedConnector<List<T>> c = new ValuedConnector<List<T>>() {

            @Override
            public List<T> query(Connection c) throws SQLException {
                List<T> ret = new ArrayList<T>();
                PreparedStatement ps = c.prepareStatement(sql);
                callback.setParams(ps);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    if (cancel != null && cancel.isCanceled())
                        break;
                    T row = callback.getRow(rs);
                    ret.add(row);
                }
                rs.close();
                ps.close();
                return ret;
            }
        };
        connect(c);
        return c.getValue();
    }

    private static interface RequiresConnection {
        public void connect(Connection c) throws SQLException;
    }

    private static abstract class ValuedConnector<T> implements RequiresConnection {

        private T value = null;

        public T getValue() {
            return value;
        }

        public abstract T query(Connection c) throws SQLException;

        public void connect(Connection c) throws SQLException {
            value = query(c);
        }

    }

}
